
--region Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[Newses_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[Newses_Insert]

IF OBJECT_ID(N'[dbo].[Newses_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[Newses_Update]

IF OBJECT_ID(N'[dbo].[Newses_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[Newses_Delete]

IF OBJECT_ID(N'[dbo].[Newses_Select]') IS NOT NULL
	DROP PROCEDURE [dbo].[Newses_Select]

--endregion

GO


--region [dbo].[Newses_Select]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Newses_Select]
	@Page int = null,
	@PageSize int = null,
    @SortBy nvarchar(255) = null,
	@SortType bit = null,
	@IdNews int = null,
	@Title nvarchar(1000) = null,
	@Summary nvarchar(1000) = null,
	@Content nvarchar(max) = null,
	@Author nvarchar(1000) = null,
	@CreatedDate datetime = null,
	@LastModifiedDate datetime = null,
	@IsActived bit = null,
	@NumberViews int = null,
	@Image nvarchar(100) = null,
	@IsVideo bit = null,
	@IsSpecial bit = null,
	@IsHot bit = null,
	@NumberComment int = null
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Declare @RowCount int  
Set @RowCount = (
					Select Count (*) 
					From [dbo].[Newses]
					WHERE
					(
						(@IdNews is null OR [Newses].[IdNews] = @IdNews)
						AND (@Title is null OR [Newses].[Title] like @Title)
						AND (@Summary is null OR [Newses].[Summary] like @Summary)
						AND (@Content is null OR [Newses].[Content] like @Content)
						AND (@Author is null OR [Newses].[Author] like @Author)
						AND (@CreatedDate is null OR [Newses].[CreatedDate] = @CreatedDate)
						AND (@LastModifiedDate is null OR [Newses].[LastModifiedDate] = @LastModifiedDate)
						AND (@IsActived is null OR [Newses].[IsActived] = @IsActived)
						AND (@NumberViews is null OR [Newses].[NumberViews] = @NumberViews)
						AND (@Image is null OR [Newses].[Image] like @Image)
						AND (@IsVideo is null OR [Newses].[IsVideo] = @IsVideo)
						AND (@IsSpecial is null OR [Newses].[IsSpecial] = @IsSpecial)
						AND (@IsHot is null OR [Newses].[IsHot] = @IsHot)
						AND (@NumberComment is null OR [Newses].[NumberComment] = @NumberComment)
					)
				)
if(@Page is null)
begin
	Set @Page = 1
end

if(@PageSize is null)
begin
	Set @PageSize = @RowCount
end

if(@SortBy is null)
begin
	Set @SortBy = 'DBNull'
end

if(@SortType is null)
begin
	Set @SortType = 1
end

SELECT *
FROM   (
			SELECT [dbo].[Newses].*,
                   ROW_NUMBER() OVER 
                   (ORDER BY
                        CASE WHEN @SortBy = 'DBNull' THEN NewID() END ASC,
						CASE WHEN @SortBy = 'IdNews' and @SortType = 1 THEN [Newses].[IdNews] END ASC,
						CASE WHEN @SortBy = 'IdNews' and @SortType = 0 THEN [Newses].[IdNews] END DESC,
						CASE WHEN @SortBy = 'Title' and @SortType = 1 THEN [Newses].[Title] END ASC,
						CASE WHEN @SortBy = 'Title' and @SortType = 0 THEN [Newses].[Title] END DESC,
						CASE WHEN @SortBy = 'Summary' and @SortType = 1 THEN [Newses].[Summary] END ASC,
						CASE WHEN @SortBy = 'Summary' and @SortType = 0 THEN [Newses].[Summary] END DESC,
						CASE WHEN @SortBy = 'Content' and @SortType = 1 THEN [Newses].[Content] END ASC,
						CASE WHEN @SortBy = 'Content' and @SortType = 0 THEN [Newses].[Content] END DESC,
						CASE WHEN @SortBy = 'Author' and @SortType = 1 THEN [Newses].[Author] END ASC,
						CASE WHEN @SortBy = 'Author' and @SortType = 0 THEN [Newses].[Author] END DESC,
						CASE WHEN @SortBy = 'CreatedDate' and @SortType = 1 THEN [Newses].[CreatedDate] END ASC,
						CASE WHEN @SortBy = 'CreatedDate' and @SortType = 0 THEN [Newses].[CreatedDate] END DESC,
						CASE WHEN @SortBy = 'LastModifiedDate' and @SortType = 1 THEN [Newses].[LastModifiedDate] END ASC,
						CASE WHEN @SortBy = 'LastModifiedDate' and @SortType = 0 THEN [Newses].[LastModifiedDate] END DESC,
						CASE WHEN @SortBy = 'IsActived' and @SortType = 1 THEN [Newses].[IsActived] END ASC,
						CASE WHEN @SortBy = 'IsActived' and @SortType = 0 THEN [Newses].[IsActived] END DESC,
						CASE WHEN @SortBy = 'NumberViews' and @SortType = 1 THEN [Newses].[NumberViews] END ASC,
						CASE WHEN @SortBy = 'NumberViews' and @SortType = 0 THEN [Newses].[NumberViews] END DESC,
						CASE WHEN @SortBy = 'Image' and @SortType = 1 THEN [Newses].[Image] END ASC,
						CASE WHEN @SortBy = 'Image' and @SortType = 0 THEN [Newses].[Image] END DESC,
						CASE WHEN @SortBy = 'IsVideo' and @SortType = 1 THEN [Newses].[IsVideo] END ASC,
						CASE WHEN @SortBy = 'IsVideo' and @SortType = 0 THEN [Newses].[IsVideo] END DESC,
						CASE WHEN @SortBy = 'IsSpecial' and @SortType = 1 THEN [Newses].[IsSpecial] END ASC,
						CASE WHEN @SortBy = 'IsSpecial' and @SortType = 0 THEN [Newses].[IsSpecial] END DESC,
						CASE WHEN @SortBy = 'IsHot' and @SortType = 1 THEN [Newses].[IsHot] END ASC,
						CASE WHEN @SortBy = 'IsHot' and @SortType = 0 THEN [Newses].[IsHot] END DESC,
						CASE WHEN @SortBy = 'NumberComment' and @SortType = 1 THEN [Newses].[NumberComment] END ASC,
						CASE WHEN @SortBy = 'NumberComment' and @SortType = 0 THEN [Newses].[NumberComment] END DESC                   
                    ) AS RowNumber 
			FROM [dbo].[Newses]
			Where 
			(
				(@IdNews is null OR [Newses].[IdNews] = @IdNews)
				AND (@Title is null OR [Newses].[Title] like @Title)
				AND (@Summary is null OR [Newses].[Summary] like @Summary)
				AND (@Content is null OR [Newses].[Content] like @Content)
				AND (@Author is null OR [Newses].[Author] like @Author)
				AND (@CreatedDate is null OR [Newses].[CreatedDate] = @CreatedDate)
				AND (@LastModifiedDate is null OR [Newses].[LastModifiedDate] = @LastModifiedDate)
				AND (@IsActived is null OR [Newses].[IsActived] = @IsActived)
				AND (@NumberViews is null OR [Newses].[NumberViews] = @NumberViews)
				AND (@Image is null OR [Newses].[Image] like @Image)
				AND (@IsVideo is null OR [Newses].[IsVideo] = @IsVideo)
				AND (@IsSpecial is null OR [Newses].[IsSpecial] = @IsSpecial)
				AND (@IsHot is null OR [Newses].[IsHot] = @IsHot)
				AND (@NumberComment is null OR [Newses].[NumberComment] = @NumberComment)
			)
		) AS Temp
WHERE  RowNumber Between (@Page * @PageSize - @PageSize + 1) And @Page * @PageSize
Return @RowCount


--end [dbo].[Newses_Select]
--endregion

GO
--=========================================================================================--

	

--region [dbo].[Newses_Insert]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Newses_Insert]
	@IdNews int OUTPUT,
	@Title nvarchar(1000),
	@Summary nvarchar(1000),
	@Content nvarchar(max),
	@Author nvarchar(1000),
	@CreatedDate datetime,
	@LastModifiedDate datetime,
	@IsActived bit,
	@NumberViews int,
	@Image nvarchar(100),
	@IsVideo bit,
	@IsSpecial bit,
	@IsHot bit,
	@NumberComment int

AS


INSERT INTO [dbo].[Newses] 
(
	[Title],
	[Summary],
	[Content],
	[Author],
	[CreatedDate],
	[LastModifiedDate],
	[IsActived],
	[NumberViews],
	[Image],
	[IsVideo],
	[IsSpecial],
	[IsHot],
	[NumberComment]
)
VALUES 
(
	@Title,
	@Summary,
	@Content,
	@Author,
	@CreatedDate,
	@LastModifiedDate,
	@IsActived,
	@NumberViews,
	@Image,
	@IsVideo,
	@IsSpecial,
	@IsHot,
	@NumberComment
)

SET @IdNews = SCOPE_IDENTITY()

--end [dbo].[Newses_Insert]
--endregion

GO
--=========================================================================================--

--region [dbo].[Newses_Update]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Newses_Update]
	@IdNews int,
	@Title nvarchar(1000),
	@Summary nvarchar(1000),
	@Content nvarchar(max),
	@Author nvarchar(1000),
	@CreatedDate datetime,
	@LastModifiedDate datetime,
	@IsActived bit,
	@NumberViews int,
	@Image nvarchar(100),
	@IsVideo bit,
	@IsSpecial bit,
	@IsHot bit,
	@NumberComment int
AS


UPDATE [dbo].[Newses] SET
	[Title] = @Title,
	[Summary] = @Summary,
	[Content] = @Content,
	[Author] = @Author,
	[CreatedDate] = @CreatedDate,
	[LastModifiedDate] = @LastModifiedDate,
	[IsActived] = @IsActived,
	[NumberViews] = @NumberViews,
	[Image] = @Image,
	[IsVideo] = @IsVideo,
	[IsSpecial] = @IsSpecial,
	[IsHot] = @IsHot,
	[NumberComment] = @NumberComment
WHERE
	[IdNews] = @IdNews

--end [dbo].[Newses_Update]
--endregion

GO
--=========================================================================================--

--region [dbo].[Newses_Delete]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Newses_Delete]
	@IdNews int
AS


DELETE FROM [dbo].[Newses]
WHERE
(
	[IdNews] = @IdNews
)

--end [dbo].[Newses_Delete]
--endregion

GO
--=========================================================================================--

